package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.omg.CORBA.ORBPackage.InconsistentTypeCode;

import com.sg.hms.Dao.ApplicationDao;
import com.sg.hms.Model.Application;
import com.sg.hms.Model.Pagebean;
import com.sg.hms.Util.JDBCUtil;
/***
 * 操作申请方法的具体实现
 * @author 吴磊
 *
 */
public class ApplicationDaoImpl implements ApplicationDao{

	@Override
	public ArrayList<Application> searchApplication(int elId, String key, Pagebean pagebean) {
		// TODO Auto-generated method stub
		ArrayList<Application> list=new ArrayList<Application>();
		Connection con=JDBCUtil.getConnection();
		String sql="select * from application where elId=? and aContent like ? limit ?,?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, elId);
			ps.setString(2, "%"+key+"%");
			ps.setInt(3,pagebean.getStart());
			ps.setInt(4, pagebean.getPageSize());
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Application(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getDate(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public ArrayList<Application> searchallApplication(int elId, String key) {
		// TODO Auto-generated method stub
		ArrayList<Application> list=new ArrayList<Application>();
		Connection con=JDBCUtil.getConnection();
		String sql="select * from application where elId=? and aContent like ?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, elId);
			ps.setString(2, "%"+key+"%");
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Application(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getDate(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public ArrayList<Application> searchallApplication(int elId, Pagebean pagebean) {
		// TODO Auto-generated method stub
		ArrayList<Application> list=new ArrayList<Application>();
		Connection con=JDBCUtil.getConnection();
		String sql="select * from application where elId=? limit ?,?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, elId);
			ps.setInt(2,pagebean.getStart());
			ps.setInt(3, pagebean.getPageSize());
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Application(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getDate(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public ArrayList<Application> searchallApplication(int elId) {
		// TODO Auto-generated method stub
		ArrayList<Application> list=new ArrayList<Application>();
		Connection con=JDBCUtil.getConnection();
		String sql="select * from application where elId=?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, elId);
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Application(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getDate(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public ArrayList<Application> someApplication(int elId, String state,Pagebean pagebean) {
		// TODO Auto-generated method stub
		ArrayList<Application> list=new ArrayList<Application>();
		Connection con=JDBCUtil.getConnection();
		String sql="select * from application where elId=? and state=? limit ?,?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, elId);
			ps.setString(2, state);
			ps.setInt(3,pagebean.getStart());
			ps.setInt(4, pagebean.getPageSize());
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Application(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getDate(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public ArrayList<Application> someApplication(int elId, String state) {
		// TODO Auto-generated method stub
		ArrayList<Application> list=new ArrayList<Application>();
		Connection con=JDBCUtil.getConnection();
		String sql="select * from application where elId=? and state=?";
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, elId);
			ps.setString(2, state);
			ResultSet rs=ps.executeQuery();
			while (rs.next()) {
				list.add(new Application(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getDate(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int addApplication(int elId, String aContent, String state, Date creatDate) {
		// TODO Auto-generated method stub
	    int temp=0;
	    Connection con=JDBCUtil.getConnection();
	    String sql="insert into application (elId,aContent,state,creatDate) values(?,?,?,?)";
	    try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, elId);
			ps.setString(2, aContent);
			ps.setString(3, state);
			ps.setDate(4, creatDate);
			temp=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}
	

}
